package ybl.qianchuan;

import java.io.File;
import java.io.IOException;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.jdbi.v3.core.Jdbi;
import ybl.qianchuan.entities.AccessToken;
import ybl.qianchuan.entities.QianchuanAweme;

public class StoreManage {

    /**
     * 初始化并返回数据库连接。
     * @return Jdbi 数据库接口实例。
     * @throws IOException 如果数据库文件创建失败。
     */
    private static Jdbi initDB() throws IOException {
        String workingDirectory = System.getProperty("user.dir");
        String dbFilePath = workingDirectory + "/dbs/qianchuan.db";
        File dbFile = new File(dbFilePath);
        if (!dbFile.exists()) {
            dbFile.getParentFile().mkdirs(); // 创建父目录
            dbFile.createNewFile(); // 创建新文件
        }

        String jdbcUrl = "jdbc:sqlite:" + dbFilePath;
        return Jdbi.create(jdbcUrl);
    }

    /**
     * 创建所需的数据库表。
     * @param jdbi Jdbi 实例。
     */
    private static void createTables(Jdbi jdbi) {
        jdbi.useHandle(handle -> {
            handle.execute("CREATE TABLE IF NOT EXISTS access_tokens (" +
                            "accessTokenName TEXT UNIQUE, " +
                            "accessToken TEXT, " +
                            "accessTokenExpires INTEGER, " +
                            "refreshToken TEXT, " +
                            "refreshTokenExpires INTEGER, " +
                            "updateAt TIMESTAMP, " +
                            "PRIMARY KEY (accessTokenName))");

            
            handle.execute("CREATE TABLE IF NOT EXISTS qianchuan_aweme (" +
                            "qianchuanId INTEGER, " +
                            "awemeId INTEGER, " +
                            "awemeShowId TEXT, " +
                            "awemeStatus TEXT, " +
                            "awemeHasUniProm TEXT, " +
                            "accessTokenName TEXT, " + // 新增accessToken字段
                            "PRIMARY KEY (qianchuanId, awemeId), " +
                            "UNIQUE (qianchuanId, awemeId))");
                        });
    }
    

    /**
     * 保存访问令牌到数据库。
     * @param m 包含令牌信息的映射。
     */
    public static void saveAccessToken(Map<String, Object> m) {
        try {
            Jdbi jdbi = initDB();
            createTables(jdbi); // 确保表被创建
    
            AccessToken token = new AccessToken();
            token.setAccessTokenName((String) m.get("accessTokenName"));
            token.setAccessToken((String) m.get("accessToken"));
            token.setAccessTokenExpires((Integer) m.get("expiresIn"));
            token.setRefreshToken((String) m.get("refreshToken"));
            token.setRefreshTokenExpires((Integer) m.get("refreshTokenExpiresIn"));
            token.setUpdateAt(Timestamp.from(Instant.now()));
    
            jdbi.useHandle(handle -> {
                // 使用UPSERT逻辑更新或插入数据
                String sql = "INSERT INTO access_tokens (accessTokenName, accessToken, accessTokenExpires, refreshToken, refreshTokenExpires, updateAt) " +
                             "VALUES (:accessTokenName, :accessToken, :accessTokenExpires, :refreshToken, :refreshTokenExpires, :updateAt) " +
                             "ON CONFLICT (accessTokenName) DO UPDATE SET " +
                             "accessToken = EXCLUDED.accessToken, accessTokenExpires = EXCLUDED.accessTokenExpires, refreshToken = EXCLUDED.refreshToken, refreshTokenExpires = EXCLUDED.refreshTokenExpires, updateAt = EXCLUDED.updateAt";
    
                handle.createUpdate(sql)
                      .bindBean(token)
                      .execute();
            });
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void saveAccessToken(AccessToken token) {
        try {
            Jdbi jdbi = initDB();
            createTables(jdbi); // 确保表被创建
            jdbi.useHandle(handle -> {
                // 使用UPSERT逻辑更新或插入数据
                String sql = "INSERT INTO access_tokens (accessTokenName, accessToken, accessTokenExpires, refreshToken, refreshTokenExpires, updateAt) " +
                             "VALUES (:accessTokenName, :accessToken, :accessTokenExpires, :refreshToken, :refreshTokenExpires, :updateAt) " +
                             "ON CONFLICT (accessTokenName) DO UPDATE SET " +
                             "accessToken = EXCLUDED.accessToken, accessTokenExpires = EXCLUDED.accessTokenExpires, refreshToken = EXCLUDED.refreshToken, refreshTokenExpires = EXCLUDED.refreshTokenExpires, updateAt = EXCLUDED.updateAt";
    
                handle.createUpdate(sql)
                      .bindBean(token)
                      .execute();
            });
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 从数据库获取最新的 AccessToken。
     * @return 最新的 AccessToken 对象，如果不存在则返回 null。
     */
    public static AccessToken getAccessToken(String accessTokenName) {
        try {
            Jdbi jdbi = initDB();
            createTables(jdbi); // 确保表被创建
            return jdbi.withHandle(handle -> 
                handle.createQuery("SELECT accessTokenName, accessToken, accessTokenExpires, refreshToken, refreshTokenExpires, updateAt FROM access_tokens WHERE accessTokenName = :accessTokenName")
                      .bind("accessTokenName", accessTokenName)
                      .mapToBean(AccessToken.class)
                      .findOne()
                      .orElse(null)
            );
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
    


    /**
     * 保存 QianchuanAweme 信息到数据库。
     * @param m 包含 QianchuanAweme 信息的映射。
     */
    public static void saveQianchuanAweme(Map<String, Object> m) {
        try {
            Jdbi jdbi = initDB();
            createTables(jdbi); // 确保表被创建
    
            QianchuanAweme aweme = new QianchuanAweme();
            aweme.setQianchuanId((Long) m.get("qianchuanId"));
            aweme.setAwemeId((Long) m.get("awemeId"));
            aweme.setAwemeShowId((String) m.get("awemeShowId"));
            aweme.setAwemeStatus((String) m.get("awemeStatus"));
            aweme.setAwemeHasUniProm((Boolean) m.get("awemeHasUniProm"));
            aweme.setAccessTokenName((String) m.get("accessTokenName"));
    
            jdbi.useHandle(handle -> handle.createUpdate("INSERT OR REPLACE INTO qianchuan_aweme (qianchuanId, awemeId, awemeShowId, awemeStatus, awemeHasUniProm, accessTokenName) VALUES (:qianchuanId, :awemeId, :awemeShowId, :awemeStatus, :awemeHasUniProm, :accessTokenName)")
                                          .bindBean(aweme)
                                          .execute());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 通过 awemeShowId 从数据库获取 QianchuanAweme 记录。
     * @param awemeShowId 要查询的 awemeShowId。
     * @return 与 awemeShowId 匹配的 QianchuanAweme 记录的列表，如果没有找到则返回空列表。
     */
    public static List<QianchuanAweme> getQianchuanAwemesByawemeShowId(String awemeShowId) {
        try {
            Jdbi jdbi = initDB();
            createTables(jdbi); // 确保表被创建
            return jdbi.withHandle(handle ->
                handle.createQuery("SELECT * FROM qianchuan_aweme WHERE awemeShowId = :awemeShowId")
                    .bind("awemeShowId", awemeShowId)
                    .mapToBean(QianchuanAweme.class)
                    .list()
            );
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static QianchuanAweme getOneQianchuanAwemesByawemeShowId(String awemeShowId) {
        try {
            Jdbi jdbi = initDB();
            createTables(jdbi); // 确保表被创建
            return jdbi.withHandle(handle ->
                handle.createQuery("SELECT * FROM qianchuan_aweme WHERE awemeShowId = :awemeShowId")
                    .bind("awemeShowId", awemeShowId)
                    .mapToBean(QianchuanAweme.class)
                    .findFirst()
                    .orElse(null) // 如果没有找到，则返回 null
            );
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static List<QianchuanAweme> getQianchuanAwemesByawemeShowIdAndAwemeHasUniProm(String awemeShowId) {
        try {
            Jdbi jdbi = initDB();
            createTables(jdbi); // 确保表被创建
            return jdbi.withHandle(handle ->
                handle.createQuery("SELECT * FROM qianchuan_aweme WHERE awemeShowId = :awemeShowId AND awemeHasUniProm = TRUE")
                    .bind("awemeShowId", awemeShowId)
                    .mapToBean(QianchuanAweme.class)
                    .list() // 获取所有匹配的记录
            );
        } catch (IOException e) {
            e.printStackTrace();
            return new ArrayList<>(); // 发生异常时返回空列表
        }
    }

    /**
     * 根据传入的前千川ID删除aweme
     *
     * @param qianchuanId 千川ID
     */
    public static void deleteQianchuanAwemeByqianchuanId(Long qianchuanId) {
        try {
            Jdbi jdbi = initDB(); // 初始化数据库连接
            createTables(jdbi); // 确保表被创建

            // 使用Jdbi的handle执行删除操作
            jdbi.useHandle(handle -> {
                String sql = "DELETE FROM qianchuan_aweme WHERE qianchuanId = :qianchuanId";
                handle.createUpdate(sql)
                      .bind("qianchuanId", qianchuanId)
                      .execute();
            });
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 根据传入的 accessTokenName删除aweme
     *
     * @param accessTokenName accessTokenName
     */
     public static void deleteQianchuanAwemeByaccessTokenName(String accessTokenName) {
        try {
            Jdbi jdbi = initDB(); // 初始化数据库连接
            createTables(jdbi); // 确保表被创建

            // 使用Jdbi的handle执行删除操作
            jdbi.useHandle(handle -> {
                String sql = "DELETE FROM qianchuan_aweme WHERE accessTokenName = :accessTokenName";
                handle.createUpdate(sql)
                      .bind("accessTokenName", accessTokenName)
                      .execute();
            });
        } catch (IOException e) {
            e.printStackTrace();
        }
     }
}